package ua.kyivenergo.cableLines.data.database;

import ua.kyivenergo.cableLines.data.GenericObject;
import ua.kyivenergo.cableLines.data.attributes.BinaryAttribute;
import ua.kyivenergo.cableLines.data.attributes.LinkAttribute;
import ua.kyivenergo.cableLines.data.attributes.StringAttribute;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Alex Kebkalo (kebka@mail.ru) on 24.04.14.
 */
public class DataManager {
    private final String CONNECT_STRING = "jdbc:mysql://localhost/power_lines?user=power_lines&password=kievenergo_power_lines";
    private Connection connect;

    public DataManager() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection(CONNECT_STRING);
    }

    public List<GenericObject> loadCableLines() throws Exception {
        ArrayList<GenericObject> cableLines = new ArrayList<>();
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select id from objects where type = 'cable_line'");
        while (resultSet.next()) {
            GenericObject nextObject = loadObject(resultSet.getInt("id"));
            if (nextObject != null) {
                cableLines.add(nextObject);
            }
        }
        return cableLines;
    }

    public byte[] getBinary(String attributeId) throws Exception {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select value from attributes_binary where attribute_id = " + attributeId);
        byte[] imgData;
        if (resultSet.next()) {
            Blob image = resultSet.getBlob(1);
            imgData = image.getBytes(1, (int) image.length());
        } else {
            return null;
        }
        return imgData;
    }

    private GenericObject loadObject(int objectId) throws Exception {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select type from objects where id = " + objectId);
        if (!resultSet.next()) {
            return null;
        }
        GenericObject newObject = new GenericObject(resultSet.getString("type"));

        loadStringAttributes(objectId, newObject);
        loadLinkAttributes(objectId, newObject);
        loadBinaryAttributes(objectId, newObject);
        return newObject;
    }

    private void loadStringAttributes(int objectId, GenericObject myObject) throws Exception {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from attributes_strings where object_id = " + objectId);
        while (resultSet.next()) {
            StringAttribute attribute = new StringAttribute(resultSet.getString("value"), resultSet.getString("description"));
            myObject.addAttribute(attribute);
        }
    }

    private void loadLinkAttributes(int objectId, GenericObject myObject) throws Exception {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from attributes_links where object_id = " + objectId);
        while (resultSet.next()) {
            GenericObject linkedObject = loadObject(resultSet.getInt("linked_object_id"));
            LinkAttribute attribute = new LinkAttribute(resultSet.getString("description"), linkedObject);
            myObject.addAttribute(attribute);
        }
    }

    private void loadBinaryAttributes(int objectId, GenericObject myObject) throws Exception {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from attributes_binary where object_id = " + objectId);
        while (resultSet.next()) {
            BinaryAttribute attribute = new BinaryAttribute(resultSet.getInt("attribute_id"), resultSet.getInt("part_number"), resultSet.getString("description"));
            myObject.addAttribute(attribute);
        }
    }
}
